package com.pur.zsms.turnover.dao.impl;

import java.util.List;

import org.apache.commons.lang.StringUtils;
import org.springframework.stereotype.Repository;

import com.pur.zsms.common.dao.GenericDao;
import com.pur.zsms.common.dao.JpaTemplate;
import com.pur.zsms.common.exception.GenericException;
import com.pur.zsms.common.support.PageController;
import com.pur.zsms.common.support.PaginationSupport;
import com.pur.zsms.cost.vo.CostVo;
import com.pur.zsms.financing.vo.FinancingVo;
import com.pur.zsms.foodmanage.food.po.FoodPo;
import com.pur.zsms.matermanage.mater.po.MaterLevelPo;
import com.pur.zsms.matermanage.mater.vo.MaterVo;
import com.pur.zsms.pmsnmanage.user.po.UserPo;
import com.pur.zsms.systemsetting.datadictionary.dao.IDataDictionaryDao;
import com.pur.zsms.systemsetting.datadictionary.po.Code;
import com.pur.zsms.systemsetting.datadictionary.po.CodeType;
import com.pur.zsms.systemsetting.datadictionary.vo.CodeVo;
import com.pur.zsms.timecontrol.dao.ITimeControlDao;
import com.pur.zsms.timecontrol.po.TimeControlPo;
import com.pur.zsms.turnover.dao.ITurnoverDao;
import com.pur.zsms.turnover.po.TurnoverPo;
import com.pur.zsms.turnover.po.TurnoverSubPo;
import com.pur.zsms.turnover.vo.TurnoverVo;

@Repository("turnoverDao")
public class TurnoverDaoImpl extends GenericDao implements ITurnoverDao {

	public PaginationSupport listFinancingVoList(PageController pageController, FinancingVo tFinancingVo) throws GenericException {
		StringBuffer ql = new StringBuffer();
		ql.append("SELECT DISTINCT t1.turnover_id,t1.cust_id as custId,t1.shop_name shopName,t1.turnoverValueTotal,t1.turnoverTypeDetialValue,t1.turnover_state,t1.turnover_date , t2.cost_id,t2.cust_id,t2.shop_name,t2.costValueTotal,t2.costTypeDetialValue,t2.cost_state,t2.cost_date,IFNULL(t2.cost_date,t1.turnover_date) AS orderby FROM (SELECT a.turnover_id, a.cust_id, b.shop_name, CONCAT( '￥', FORMAT(a.turnover_value_total, 2) ) AS turnoverValueTotal, GROUP_CONCAT( c.code_name, ':￥', FORMAT(a2.turnover_value, 2), '<br/>' ORDER BY ph_code_id SEPARATOR '' ) AS turnoverTypeDetialValue, a.turnover_state, a.turnover_date FROM y_turnover a, y_turnover_sub a2, t_shop b, t_code c WHERE b.shop_id = a.cust_id AND a.turnover_id = a2.turnover_id AND c.code_type_id = 'turnoverType' AND c.code_id = a2.turnover_type ");

		ql.append(" AND a.`turnover_date` >= '" + tFinancingVo.getDateStart() + "'");
		ql.append(" AND a.`turnover_date` <= '" + tFinancingVo.getDateEnd() + "'");

		if (tFinancingVo.getCustId() != 0) {
			ql.append(" and a.cust_id = '" + tFinancingVo.getCustId() + "'");
		}
		if (tFinancingVo.getTurnoverDate() != null && !tFinancingVo.getTurnoverDate().equals("")) {
			ql.append(" and a.turnover_date = '" + tFinancingVo.getTurnoverDate() + "'");
		}
		ql.append(" GROUP BY a2.turnover_id ) t1 LEFT JOIN (SELECT a.cost_id , a.cust_id, b.shop_name, CONCAT( '￥', FORMAT(a.cost_value_total, 2) ) AS costValueTotal, GROUP_CONCAT( c.code_name, ':￥', FORMAT(a2.cost_value, 2), '<br/>' ORDER BY ph_code_id SEPARATOR '' ) AS costTypeDetialValue, a.cost_state, a.cost_date FROM c_cost a, c_cost_sub a2, t_shop b, t_code c WHERE b.shop_id = a.cust_id AND a.cost_id = a2.cost_id AND c.code_type_id = 'costType' AND c.code_id = a2.cost_type");
		if (tFinancingVo.getCustId() != 0) {
			ql.append(" and a.cust_id = '" + tFinancingVo.getCustId() + "'");
		}
		if (tFinancingVo.getTurnoverDate() != null && !tFinancingVo.getTurnoverDate().equals("")) {
			ql.append(" and a.turnover_date = '" + tFinancingVo.getTurnoverDate() + "'");
		}
		ql.append(" GROUP BY a2.cost_id ) t2 ON t2.cost_date = t1.turnover_date");

		ql.append(" UNION ");
		ql.append("SELECT t1.turnover_id, t1.cust_id as custId,t1.shop_name shopName, t1.turnoverValueTotal, t1.turnoverTypeDetialValue, t1.turnover_state, t1.turnover_date, t2.cost_id, t2.cust_id, t2.shop_name, t2.costValueTotal, t2.costTypeDetialValue, t2.cost_state, t2.cost_date,IFNULL(t2.cost_date,t1.turnover_date) AS orderby FROM (SELECT a.cost_id, a.cust_id, b.shop_name, CONCAT( '￥', FORMAT(a.cost_value_total, 2) ) AS costValueTotal, GROUP_CONCAT( c.code_name, ':￥', FORMAT(a2.cost_value, 2), '<br/>' ORDER BY ph_code_id SEPARATOR '' ) AS costTypeDetialValue, a.cost_state, a.cost_date FROM c_cost a, c_cost_sub a2, t_shop b, t_code c WHERE b.shop_id = a.cust_id AND a.cost_id = a2.cost_id AND c.code_type_id = 'costType' AND c.code_id = a2.cost_type ");

		ql.append(" AND a.`cost_date` >= '" + tFinancingVo.getDateStart() + "'");
		ql.append(" AND a.`cost_date` <= '" + tFinancingVo.getDateEnd() + "'");
		if (tFinancingVo.getCustId() != 0) {
			ql.append(" and a.cust_id = '" + tFinancingVo.getCustId() + "'");
		}
		if (tFinancingVo.getTurnoverDate() != null && !tFinancingVo.getTurnoverDate().equals("")) {
			ql.append(" and a.turnover_date = '" + tFinancingVo.getTurnoverDate() + "'");
		}
		ql.append(" GROUP BY a2.cost_id) t2 LEFT JOIN (SELECT a.turnover_id, a.cust_id, b.shop_name, CONCAT( '￥', FORMAT(a.turnover_value_total, 2) ) AS turnoverValueTotal, GROUP_CONCAT( c.code_name, ':￥', FORMAT(a2.turnover_value, 2), '<br/>' ORDER BY ph_code_id SEPARATOR '' ) AS turnoverTypeDetialValue, a.turnover_state, a.turnover_date FROM y_turnover a, y_turnover_sub a2, t_shop b, t_code c WHERE b.shop_id = a.cust_id AND a.turnover_id = a2.turnover_id AND c.code_type_id = 'turnoverType' AND c.code_id = a2.turnover_type ");
		if (tFinancingVo.getCustId() != 0) {
			ql.append(" and a.cust_id = '" + tFinancingVo.getCustId() + "'");
		}
		if (tFinancingVo.getTurnoverDate() != null && !tFinancingVo.getTurnoverDate().equals("")) {
			ql.append(" and a.turnover_date = '" + tFinancingVo.getTurnoverDate() + "'");
		}
		ql.append(" GROUP BY a2.turnover_id) t1 ON t2.cost_date = t1.turnover_date  ORDER BY orderby DESC ");

		return this.findByNativeQuery(ql.toString(), pageController);
	}

	public PaginationSupport auditFinancingList(PageController pageController, FinancingVo tFinancingVo) throws GenericException {
		StringBuffer ql = new StringBuffer();
		ql.append("SELECT DISTINCT t1.turnover_id,t1.cust_id as custId,t1.shop_name shopName,t1.turnoverValueTotal,t1.turnoverTypeDetialValue,t1.turnover_state,t1.turnover_date , t2.cost_id,t2.cust_id,t2.shop_name,t2.costValueTotal,t2.costTypeDetialValue,t2.cost_state,t2.cost_date,IFNULL(t2.cost_date,t1.turnover_date) AS orderby FROM (SELECT a.turnover_id, a.cust_id, b.shop_name, CONCAT( '￥', FORMAT(a.turnover_value_total, 2) ) AS turnoverValueTotal, GROUP_CONCAT( c.code_name, ':￥', FORMAT(a2.turnover_value, 2), '<br/>' ORDER BY ph_code_id SEPARATOR '' ) AS turnoverTypeDetialValue, a.turnover_state, a.turnover_date FROM y_turnover a, y_turnover_sub a2, t_shop b, t_code c WHERE b.shop_id = a.cust_id AND a.turnover_id = a2.turnover_id AND c.code_type_id = 'turnoverType' AND c.code_id = a2.turnover_type ");

		ql.append(" AND a.`turnover_date` like  '%" + tFinancingVo.getDateMonth() + "%'");

		if (tFinancingVo.getCustId() != 0) {
			ql.append(" and a.cust_id = '" + tFinancingVo.getCustId() + "'");
		}
		if (tFinancingVo.getTurnoverDate() != null && !tFinancingVo.getTurnoverDate().equals("")) {
			ql.append(" and a.turnover_date = '" + tFinancingVo.getTurnoverDate() + "'");
		}
		ql.append(" GROUP BY a2.turnover_id ) t1 LEFT JOIN (SELECT a.cost_id , a.cust_id, b.shop_name, CONCAT( '￥', FORMAT(a.cost_value_total, 2) ) AS costValueTotal, GROUP_CONCAT( c.code_name, ':￥', FORMAT(a2.cost_value, 2), '<br/>' ORDER BY ph_code_id SEPARATOR '' ) AS costTypeDetialValue, a.cost_state, a.cost_date FROM c_cost a, c_cost_sub a2, t_shop b, t_code c WHERE b.shop_id = a.cust_id AND a.cost_id = a2.cost_id AND c.code_type_id = 'costType' AND c.code_id = a2.cost_type");
		if (tFinancingVo.getCustId() != 0) {
			ql.append(" and a.cust_id = '" + tFinancingVo.getCustId() + "'");
		}
		if (tFinancingVo.getTurnoverDate() != null && !tFinancingVo.getTurnoverDate().equals("")) {
			ql.append(" and a.turnover_date = '" + tFinancingVo.getTurnoverDate() + "'");
		}
		ql.append(" GROUP BY a2.cost_id ) t2 ON t2.cost_date = t1.turnover_date");

		ql.append(" UNION ");
		ql.append("SELECT t1.turnover_id, t1.cust_id as custId,t1.shop_name shopName, t1.turnoverValueTotal, t1.turnoverTypeDetialValue, t1.turnover_state, t1.turnover_date, t2.cost_id, t2.cust_id, t2.shop_name, t2.costValueTotal, t2.costTypeDetialValue, t2.cost_state, t2.cost_date,IFNULL(t2.cost_date,t1.turnover_date) AS orderby FROM (SELECT a.cost_id, a.cust_id, b.shop_name, CONCAT( '￥', FORMAT(a.cost_value_total, 2) ) AS costValueTotal, GROUP_CONCAT( c.code_name, ':￥', FORMAT(a2.cost_value, 2), '<br/>' ORDER BY ph_code_id SEPARATOR '' ) AS costTypeDetialValue, a.cost_state, a.cost_date FROM c_cost a, c_cost_sub a2, t_shop b, t_code c WHERE b.shop_id = a.cust_id AND a.cost_id = a2.cost_id AND c.code_type_id = 'costType' AND c.code_id = a2.cost_type ");

		ql.append(" AND a.`cost_date` like  '%" + tFinancingVo.getDateMonth() + "%'");
		if (tFinancingVo.getCustId() != 0) {
			ql.append(" and a.cust_id = '" + tFinancingVo.getCustId() + "'");
		}
		if (tFinancingVo.getTurnoverDate() != null && !tFinancingVo.getTurnoverDate().equals("")) {
			ql.append(" and a.turnover_date = '" + tFinancingVo.getTurnoverDate() + "'");
		}
		ql.append(" GROUP BY a2.cost_id) t2 LEFT JOIN (SELECT a.turnover_id, a.cust_id, b.shop_name, CONCAT( '￥', FORMAT(a.turnover_value_total, 2) ) AS turnoverValueTotal, GROUP_CONCAT( c.code_name, ':￥', FORMAT(a2.turnover_value, 2), '<br/>' ORDER BY ph_code_id SEPARATOR '' ) AS turnoverTypeDetialValue, a.turnover_state, a.turnover_date FROM y_turnover a, y_turnover_sub a2, t_shop b, t_code c WHERE b.shop_id = a.cust_id AND a.turnover_id = a2.turnover_id AND c.code_type_id = 'turnoverType' AND c.code_id = a2.turnover_type ");
		if (tFinancingVo.getCustId() != 0) {
			ql.append(" and a.cust_id = '" + tFinancingVo.getCustId() + "'");
		}
		if (tFinancingVo.getTurnoverDate() != null && !tFinancingVo.getTurnoverDate().equals("")) {
			ql.append(" and a.turnover_date = '" + tFinancingVo.getTurnoverDate() + "'");
		}
		ql.append(" GROUP BY a2.turnover_id) t1 ON t2.cost_date = t1.turnover_date  ORDER BY orderby DESC ");

		return this.findByNativeQuery(ql.toString(), pageController);
	}

	public List<TurnoverPo> getTurnoverPo(TurnoverVo turnoverVo) throws GenericException {
		JpaTemplate template = this.getTemplate();
		StringBuffer ql = new StringBuffer();
		ql.append(" select o ");
		ql.append(" from TurnoverPo o where 1=1 ");
		if (turnoverVo.getCustId() != 0) {
			ql.append(" and o.custId ='" + turnoverVo.getCustId() + "'");
		}
		if (turnoverVo.getTurnoverDate() != null && !"".equals(turnoverVo.getTurnoverDate())) {
			ql.append(" and o.turnoverDate ='" + turnoverVo.getTurnoverDate() + "'");
		}
		if (turnoverVo.getTurnoverType() != 0) {
			ql.append(" and o.turnoverType ='" + turnoverVo.getTurnoverType() + "'");
		}
		if (turnoverVo.getTurnoverState() != 0) {
			ql.append(" and o.turnoverState ='" + turnoverVo.getTurnoverState() + "'");
		}
		if (turnoverVo.getTurnoverMonth() != null && !"".equals(turnoverVo.getTurnoverMonth())) {
			ql.append(" and o.turnoverDate like '" + turnoverVo.getTurnoverMonth() + "%'");
		}

		List<TurnoverPo> list = template.find(ql.toString(), null);
		return list;
	}

	public void addTurnoverPo(TurnoverPo c) throws GenericException {
		this.persist(c);
	}

	public void updateTurnoverPo(TurnoverPo c) throws GenericException {
		this.merge(c);
	}

	public void addTurnoverSubPo(TurnoverSubPo c) throws GenericException {
		this.persist(c);
	}

	public List<Object> getMaxAddId(FinancingVo tFinancingVo) {
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT MAX(ys.turnover_sub_id) FROM y_turnover_sub ys,y_turnover y1  WHERE ys.turnover_id = y1.turnover_id AND y1.cust_id = '"
				+ tFinancingVo.getCustId() + "' AND y1.turnover_date='" + tFinancingVo.getDate() + "'");
		List list = this.entityManager.createNativeQuery(sql.toString()).getResultList();
		return list;
	}

	public PaginationSupport listTurnoverSubList(PageController pageController, TurnoverVo turnoverVo) throws GenericException {
		StringBuffer ql = new StringBuffer();
		ql.append("SELECT ys.turnover_id,ys.turnover_sub_id,ys.turnover_type,ys.turnover_value,ys.turnover_remark FROM y_turnover_sub ys, y_turnover y1 WHERE ys.turnover_id = y1.turnover_id AND y1.cust_id = '"
				+ turnoverVo.getCustId() + "' AND y1.turnover_date = '" + turnoverVo.getTurnoverDate() + "' ");
		return this.findByNativeQuery(ql.toString(), pageController);
	}

	public void deleteTurnover(FinancingVo c) throws GenericException {
		JpaTemplate template = this.getTemplate();
		StringBuffer ql = new StringBuffer();
		ql.append(" delete TurnoverPo o where o.turnoverId=" + c.getTurnoverId());
		template.executeUpdate(ql.toString());

		ql = new StringBuffer("");
		ql.append(" delete TurnoverSubPo o where o.turnoverId=" + c.getTurnoverId());
		template.executeUpdate(ql.toString());

	}

	public PaginationSupport turnoverDetailForManager(PageController pageController, TurnoverVo turnoverVo) throws GenericException {
		StringBuffer ql = new StringBuffer();
		ql.append("SELECT c1.turnover_type, SUM(c1.turnover_value) FROM y_turnover_sub c1, y_turnover c2 WHERE c2.turnover_id = c1.turnover_id AND c2.turnover_date>= '"
				+ turnoverVo.getTurnoverDateStart() + "' AND c2.turnover_date<= '" + turnoverVo.getTurnoverDateEnd() + "' AND c2.cust_id='"
				+ turnoverVo.getCustId() + "' GROUP BY c1.turnover_type ");
		return this.findByNativeQuery(ql.toString(), pageController);
	}
}
